<?php
/**
 * User: hufeng
 * Date: 2016/12/21
 * Desc: excel处理
 */
namespace Common\Util;

class Excel{

    //生成xls文件
    function createXls(&$dataArr,$filename){
        $objPHPExcel = new \PHPExcel();
        $excelRow = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ','BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK','BL','BM','BN','BO','BP','BQ','BR','BS','BT','BU','BV','BW','BX','BY','BZ','CA','CB','CC','CD','CE','CF','CG','CH','CI','CJ','CK','CL','CM','CN','CO','CP','CQ','CR','CS','CT','CU','CV','CW','CX','CY','CZ','DA','DB','DC','DD','DE','DF','DG','DH','DI','DJ','DK','DL','DM','DN','DO','DP','DQ','DR','DS','DT','DU','DV','DW','DX','DY','DZ');
        $i = 1;
        foreach($dataArr as $val){
            if(is_array($val)){
                $j = 0;
                foreach($val as $v){
                    $objPHPExcel->getActiveSheet()->setCellValueExplicit($excelRow[$j].$i, $v,\PHPExcel_Cell_DataType::TYPE_STRING);
                    $j++;
                }
            }else{
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$i, $val,\PHPExcel_Cell_DataType::TYPE_STRING);
            }
            $i++;
        }
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save(PUBLIC_DATA_PATH.'tmp/'.$filename.'.xls');
        return PUBLIC_DATA_PATH.'tmp/'.$filename.'.xls';
    }

    //解析xls文件
    function parseXls($file){
        $objReader = \PHPExcel_IOFactory::createReader('Excel5');
        if(!$objReader->canRead($file)){
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
        }
        $objPHPExcel = $objReader->load($file);
        /**读取excel文件中的第一个工作表*/
        $currentSheet = $objPHPExcel->getSheet(0);
        /**取得最大的列号*/
        $allColumn = $currentSheet->getHighestColumn();

        /**取得一共有多少行*/
        $allRow = $currentSheet->getHighestRow();
        /**从第二行开始输出，因为excel表中第一行为列名*/
        $res = [];
        for($currentRow = 1;$currentRow <= $allRow;$currentRow++){
            /**从第A列开始输出*/
            for($currentColumn= 'A';$currentColumn<= $allColumn; $currentColumn++){
                $val = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65,$currentRow)->getValue();/**ord()将字符转为十进制数*/
                $res[$currentColumn.$currentRow] = $val;
            }
        }
        return $res;
    }
}